package com.netcracker.util.dao.impl;

import com.netcracker.db.exception.DBException;
import com.netcracker.db.DBManager;
import com.netcracker.entity.Book;
import com.netcracker.entity.record.BookRecord;
import com.netcracker.util.dao.BookDAO;
import com.netcracker.util.dao.DAOFacade;
import com.netcracker.util.dao.closer.DAOCloseHelper;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDAOImpl extends DAOCloseHelper implements BookDAO {
        
    private static final String BOOK_COL_BOOKID = "BOOKID";
    private static final String BOOK_COL_BOOKNAME = "BOOKNAME";
    private static final String BOOK_COL_BOOKYEAR = "BOOKYEAR";
    private static final String BOOK_COL_USERID = "USERID";
    private static final String BOOK_COL_PUBLISHERID = "PUBLISHERID";
    private static final String BOOK_COL_PUBLICDATE = "PUBLICDATE";

    private static final String BOOK_FILE_COL_BOOKFILENAME = "BOOKFILENAME";
    private static final String BOOK_FILE_COL_BOOKFILE = "BOOKFILE";
    
    private static final String BOOK_IMAGE_COL_BOOKIMAGENAME = "BOOKIMAGENAME";
    private static final String BOOK_IMAGE_COL_BOOKIMAGE = "BOOKIMAGE";
    
    private static final String BOOK_INFO_COL_NUMBEROFPAGES = "NUMBEROFPAGES";
    private static final String BOOK_INFO_COL_EDITION = "EDITION";
    private static final String BOOK_INFO_COL_DESCRIPTION = "DESCRIPTION";
    private static final String BOOK_INFO_COL_LANGUAGEID = "LANGUAGEID";
    
    private static final String REMOVE_BOOK_RECORD = "DELETE FROM books WHERE bookid = ?";
    private static final String REMOVE_BOOK_AUTHOR_RECORD = "DELETE FROM bookauthor "
            + "WHERE bookid = ? AND authorid = ?";
    private static final String REMOVE_BOOK_GENRE_RECORD = "DELETE FROM bookgenre "
            + "WHERE bookid = ? AND genreid = ?";
        
    private static final String GET_NEXT_BOOKID = "SELECT BOOKSEQUENCE.nextval FROM DUAL";
        
    private static final String CREATE_BOOK_RECORD = "INSERT INTO books VALUES (?,?,?,?,?,?)";
    private static final String CREATE_BOOK_INFO_RECORD = "INSERT INTO bookinfo VALUES (?,?,?,?,?)";
    private static final String CREATE_BOOK_IMAGE_RECORD = "INSERT INTO bookimage VALUES (?,?,?)";
    private static final String CREATE_BOOK_FILE_RECORD = "INSERT INTO bookfile VALUES (?,?,?)";
    private static final String CREATE_BOOK_GENRE_RECORD = "INSERT INTO bookgenre VALUES (?,?)";
    private static final String CREATE_BOOK_AUTHOR_RECORDS = "INSERT INTO bookauthor VALUES (?,?)";
    
    private static final String UPDATE_BOOK_RECORD = "UPDATE books SET bookname = ?, bookyear = ?, "
            + "userid = ?, publisherid = ?, publicdate = ? WHERE bookid = ?";
    private static final String UPDATE_BOOK_INFO_RECORD = "UPDATE bookinfo SET numberofpages = ?, "
            + "edition = ?, description = ?, languageid = ? WHERE bookid = ?";
    
    private static final String FIND_ALL_BOOKS = "SELECT * FROM books b LEFT JOIN bookinfo i "
            + "ON i.bookid = b.bookid";
    private static final String BOOK_COUNT = "SELECT COUNT(*) FROM books";
    private static final String FIND_BOOKS_BY_NAME = "SELECT * FROM books b LEFT JOIN bookinfo i "
            + "ON i.bookid = b.bookid WHERE UPPER(b.bookname) LIKE ?";
    private static final String FIND_BOOKS_BY_USER = "SELECT * FROM books b LEFT JOIN bookinfo i "
            + "ON i.bookid = b.bookid WHERE b.userid = ?";
    private static final String FIND_BOOKS_BY_PUBLISHER = "SELECT * FROM books b LEFT JOIN bookinfo i "
            + "ON i.bookid = b.bookid WHERE b.publisherid = ?";
    private static final String FIND_BOOKS_BY_GENRE = "SELECT * FROM bookgenre g RIGHT JOIN books b "
            + "ON b.bookid = g.bookid LEFT JOIN bookinfo i ON i.bookid = b.bookid WHERE g.genreid = ?";
    private static final String FIND_BOOKS_BY_AUTHOR = "SELECT * FROM bookauthor a RIGHT JOIN books b "
            + "ON b.bookid = a.bookid LEFT JOIN bookinfo i ON i.bookid = b.bookid WHERE a.authorid = ?";
    private static final String FIND_BOOK_BY_ID = "SELECT * FROM books b LEFT JOIN bookinfo i "
            + "ON i.bookid = b.bookid WHERE b.bookid = ?";
    private static final String FIND_BOOK_IMAGE = "SELECT * FROM bookimage WHERE bookid = ?";
    private static final String FIND_BOOK_FILE = "SELECT * FROM bookfile WHERE bookid = ?";
    
    private DBManager dbManager;
    private DAOFacade daoFacade;
    
    public BookDAOImpl(DBManager dbManager, DAOFacade daoFacade) {
        this.dbManager = dbManager;
        this.daoFacade = daoFacade;
    }
    
    @Override
    public void setDAOFacade(DAOFacade daoFacade) {
        this.daoFacade = daoFacade;
    }
    
    @Override
    public void setDBManager(DBManager dbManager) {
        this.dbManager = dbManager;
    }
    
    @Override
    public void create(BookRecord book) throws DBException, IOException {
        try {
            dbManager.commit();       
            int bookID = getNextBookID();
            book.setId(bookID);
            createBookRecord(book);
            createBookInfoRecord(book);
            createBookImageRecord(book);
            createBookFileRecord(book);
            createBookAuthorRecords(bookID,book.getAuthorsIDs());
            createBookGenreRecords(bookID,book.getGenreIDs());
            dbManager.commit();
        } catch (DBException | IOException ex) {
            dbManager.rollback();
            throw ex;
        }
    }

    @Override
    public void edit(BookRecord book) throws DBException {
        try {
            dbManager.commit();            
            updateBookRecord(book);
            updateBookInfoRecord(book);
            updateBookAuthorRecords(book);
            updateBookGenreRecords(book);
            dbManager.commit();
        } catch (DBException ex) {
            dbManager.rollback();
            throw ex;
        }
    }

    @Override
    public void remove(int bookID) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            dbManager.commit();            
            preparedStatement = dbManager.prepareStatement(REMOVE_BOOK_RECORD);
            preparedStatement.setInt(1, bookID);
            preparedStatement.executeQuery();
            dbManager.commit();
        } catch (SQLException ex) {
            dbManager.rollback();
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public Book find(int bookID) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = dbManager.prepareStatement(FIND_BOOK_BY_ID);
            preparedStatement.setInt(1, bookID);
            return getBooks(preparedStatement).get(0);
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public List<Book> findAll() throws DBException {
        ResultSet resultSet = null;
        List<Book> books;
        try {
            resultSet = dbManager.execSQL(FIND_ALL_BOOKS);
            books = new ArrayList<>();
            while(resultSet.next()) {
                books.add(getBookEntity(resultSet));
            }
            return books;
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeResultSet(resultSet);
        }
    }

    @Override
    public int count() throws DBException {
        ResultSet resultSet = null;
        try {
            resultSet = dbManager.execSQL(BOOK_COUNT);
            int count = 0;
            while(resultSet.next()){
                count = resultSet.getInt(1);
            }
            return count;
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeResultSet(resultSet);
        }
    }

    @Override
    public List<Book> findByAuthor(int authorID) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = dbManager.prepareStatement(FIND_BOOKS_BY_AUTHOR);
            preparedStatement.setInt(1, authorID);
            return getBooks(preparedStatement);
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public List<Book> findByGenre(int genreID) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = dbManager.prepareStatement(FIND_BOOKS_BY_GENRE);
            preparedStatement.setInt(1, genreID);
            return getBooks(preparedStatement);
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public List<Book> findByPublisher(int publisherID) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = dbManager.prepareStatement(FIND_BOOKS_BY_PUBLISHER);
            preparedStatement.setInt(1, publisherID);
            return getBooks(preparedStatement);
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public List<Book> findByUser(int userID) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = dbManager.prepareStatement(FIND_BOOKS_BY_USER);
            preparedStatement.setInt(1, userID);
            return getBooks(preparedStatement);
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public List<Book> findByName(String name) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = dbManager.prepareStatement(FIND_BOOKS_BY_NAME);
            preparedStatement.setString(1, "%" + name.toUpperCase() + "%");
            return getBooks(preparedStatement);
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }
    
    private List<Book> getBooks(PreparedStatement preparedStatement) throws DBException {
        ResultSet resultSet = null;
        List<Book> books;
        try{
            resultSet = preparedStatement.executeQuery();
            books = new ArrayList<>();
            while(resultSet.next()) {
                books.add(getBookEntity(resultSet));
            }
            return books;
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeResultSet(resultSet);
        }
    }
    
    private Book getBookEntity(ResultSet resultSet) throws SQLException, DBException {
        Book book = new Book();
        book.setId(resultSet.getInt(BOOK_COL_BOOKID));
        book.setName(resultSet.getString(BOOK_COL_BOOKNAME));
        book.setYear(resultSet.getDate(BOOK_COL_BOOKYEAR));
        book.setUser(daoFacade.getUserDAO().find(resultSet.getInt(BOOK_COL_USERID)));
        book.setPublisher(daoFacade.getPublisherDAO().find(resultSet.getInt(BOOK_COL_PUBLISHERID)));
        book.setDate(resultSet.getDate(BOOK_COL_PUBLICDATE));
        book.setNumberOfPages(resultSet.getInt(BOOK_INFO_COL_NUMBEROFPAGES));
        book.setEdition(resultSet.getInt(BOOK_INFO_COL_EDITION));
        book.setDescription(resultSet.getString(BOOK_INFO_COL_DESCRIPTION));
        book.setLanguage(daoFacade.getLanguageDAO().find(resultSet.getInt(BOOK_INFO_COL_LANGUAGEID)));
        book.setGenres(daoFacade.getGenreDAO().findBookGenres(book.getId()));
        book.setAuthors(daoFacade.getAuthorDAO().findBookAuthors(book.getId()));
        return book;
    }

    private void createBookRecord(BookRecord book) throws DBException { 
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = dbManager.prepareStatement(CREATE_BOOK_RECORD);
            preparedStatement.setInt(1, book.getId());
            preparedStatement.setString(2, book.getName());
            preparedStatement.setDate(3, new Date(book.getYear().getTime()));
            preparedStatement.setInt(4, book.getUserID());
            preparedStatement.setInt(5, book.getPublisherID());
            preparedStatement.setDate(6, new Date(book.getPublicDate().getTime()));
            preparedStatement.executeQuery();
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }

    private void createBookInfoRecord(BookRecord book) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = dbManager.prepareStatement(CREATE_BOOK_INFO_RECORD);
            preparedStatement.setInt(1,book.getId());
            preparedStatement.setInt(2, book.getNumberOfPages());
            preparedStatement.setInt(3, book.getEdition());
            preparedStatement.setString(4, book.getDescription());
            preparedStatement.setInt(5, book.getLanguageID());
            preparedStatement.executeQuery();
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }

    private void createBookAuthorRecords(int bookID, List<Integer> authorIDs) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = dbManager.prepareStatement(CREATE_BOOK_AUTHOR_RECORDS);
            for(Integer authorID : authorIDs) {
                preparedStatement.setInt(1, bookID);
                preparedStatement.setInt(2, authorID);
                preparedStatement.executeQuery();
                preparedStatement.close();
            }
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }

    private void createBookGenreRecords(int bookID, List<Integer> genreIDs) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = dbManager.prepareStatement(CREATE_BOOK_GENRE_RECORD);
            for(Integer genreID : genreIDs) {
                preparedStatement.setInt(1, bookID);
                preparedStatement.setInt(2, genreID);
                preparedStatement.executeQuery();
            }
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }

    private void updateBookRecord(BookRecord book) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = dbManager.prepareStatement(UPDATE_BOOK_RECORD);
            preparedStatement.setString(1, book.getName());
            preparedStatement.setDate(2, new Date(book.getYear().getTime()));
            preparedStatement.setInt(3, book.getUserID());
            preparedStatement.setInt(4, book.getPublisherID());
            preparedStatement.setDate(5, new Date(book.getPublicDate().getTime()));
            preparedStatement.setInt(6, book.getId());
            preparedStatement.executeQuery();
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }

    private void updateBookInfoRecord(BookRecord book) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = dbManager.prepareStatement(UPDATE_BOOK_INFO_RECORD);
            preparedStatement.setInt(1, book.getNumberOfPages());
            preparedStatement.setInt(2, book.getEdition());
            preparedStatement.setString(3, book.getDescription());
            preparedStatement.setInt(4, book.getLanguageID());
            preparedStatement.setInt(5, book.getId());
            preparedStatement.executeQuery();
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }

    private void updateBookAuthorRecords(BookRecord book) throws DBException {          
        List<Integer> updatedBookAuthors = book.getAuthorsIDs();
        List<Integer> oldBookAuthors = daoFacade.getAuthorDAO().getBookAuthorsIds(book.getId());        
        List<Integer> addAuthorList = new ArrayList<>(updatedBookAuthors); 
        addAuthorList.removeAll(oldBookAuthors);
        oldBookAuthors.removeAll(updatedBookAuthors);
        removeBookAuthorRecords(book.getId(), oldBookAuthors);
        createBookAuthorRecords(book.getId(), addAuthorList);
    }

    private void updateBookGenreRecords(BookRecord book) throws DBException {
        List<Integer> updatedBookGenres = book.getGenreIDs();
        List<Integer> oldBookGenres = daoFacade.getGenreDAO().findBookGenreIds(book.getId());        
        List<Integer> addGenreList = new ArrayList<>(updatedBookGenres); 
        addGenreList.removeAll(oldBookGenres);
        oldBookGenres.removeAll(updatedBookGenres);
        removeBookGenreRecords(book.getId(), oldBookGenres);
        createBookGenreRecords(book.getId(), addGenreList);
     }

    private void removeBookAuthorRecords(int bookID, List<Integer> authorIDs) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = dbManager.prepareStatement(REMOVE_BOOK_AUTHOR_RECORD); 
            for(Integer authorID : authorIDs) {     
                preparedStatement.setInt(1, bookID);
                preparedStatement.setInt(2, authorID);
                preparedStatement.executeQuery();  
            } 
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(),ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }
    
    private void removeBookGenreRecords(int bookID, List<Integer> genreIDs) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = dbManager.prepareStatement(REMOVE_BOOK_GENRE_RECORD); 
            for(Integer genreID : genreIDs) {     
                preparedStatement.setInt(1, bookID);
                preparedStatement.setInt(2, genreID);
                preparedStatement.executeQuery();  
            } 
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(),ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public File findBookImage(int bookID) throws DBException, IOException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        FileOutputStream fos;
        InputStream is;
        try{
            preparedStatement = dbManager.prepareStatement(FIND_BOOK_IMAGE);
            preparedStatement.setInt(1, bookID);
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            File image = new File(resultSet.getString(BOOK_IMAGE_COL_BOOKIMAGENAME));
            fos = new FileOutputStream(image);
            byte[] buffer = new byte[1];
            is = resultSet.getBinaryStream(BOOK_IMAGE_COL_BOOKIMAGE);
            while (is.read(buffer) > 0) {
                fos.write(buffer);
            }
            is.close();
            fos.flush();
            fos.close();
            return image;
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } catch (IOException ex) {
            throw ex;
        } finally {
            closeResultSet(resultSet);
            closeStatement(preparedStatement);
        }
    }

    @Override
    public File findBookFile(int bookID) throws DBException, IOException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        File bookFile;
        FileOutputStream fos;
        InputStream is;
        try{
            preparedStatement = dbManager.prepareStatement(FIND_BOOK_FILE);
            preparedStatement.setInt(1, bookID);
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            bookFile = new File(resultSet.getString(BOOK_FILE_COL_BOOKFILENAME));
            fos = new FileOutputStream(bookFile);
            byte[] buffer = new byte[1];
            is = resultSet.getBinaryStream(BOOK_FILE_COL_BOOKFILE);
            while (is.read(buffer) > 0) {
                fos.write(buffer);
            }
            is.close();
            fos.flush();
            fos.close();
            return bookFile;
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } catch (IOException ex) {
            throw ex;
        } finally {
            closeResultSet(resultSet);
            closeStatement(preparedStatement);
        }
    }
    
    private int getNextBookID() throws DBException {
        ResultSet resultSet = null;
        try {
            resultSet = dbManager.execSQL(GET_NEXT_BOOKID);
            resultSet.next();
            return resultSet.getInt(1);
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeResultSet(resultSet);
        }
    }

    private void createBookFileRecord(BookRecord book) throws DBException, IOException {
        PreparedStatement preparedStatement = null;
        try {
            FileInputStream fis = new FileInputStream(book.getFile());
            preparedStatement = dbManager.prepareStatement(CREATE_BOOK_FILE_RECORD);
            preparedStatement.setInt(1, book.getId());
            preparedStatement.setString(2, book.getFile().getName());
            preparedStatement.setBinaryStream(3, fis, (int) book.getFile().length());
            preparedStatement.executeQuery();
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } catch (IOException ex) {
            throw ex;
        } finally {
            closeStatement(preparedStatement);
        }
    }

    private void createBookImageRecord(BookRecord book) throws DBException, IOException {
        PreparedStatement preparedStatement = null;
        try {
            FileInputStream fis = new FileInputStream(book.getImage());
            preparedStatement = dbManager.prepareStatement(CREATE_BOOK_IMAGE_RECORD);
            preparedStatement.setInt(1, book.getId());
            preparedStatement.setString(2, book.getImage().getName());
            preparedStatement.setBinaryStream(3, fis, (int) book.getImage().length());
            preparedStatement.executeQuery();
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } catch (IOException ex) {
            throw ex;
        } finally {
            closeStatement(preparedStatement);
        }
    }

}
